DataDirect Test^™ Tutorial

This DataDirect Test tutorial explains how to use the most important features of DataDirect Test (and the JDBC API) and assumes that you can connect to a database with the standard available demo table or fine-tune the sample SQL statements shown in this example as appropriate for your environment.

NOTE: The step-by-step examples used in this tutorial do not show typical clean-up routines (for example, closing result sets and connections). These steps have been omitted to simplify the examples. Do not forget to add these steps when you use equivalent code in your applications.

Configuring DataDirect Test^™

The default DataDirect Test configuration file is:

install_dir/testforjdbc/Config.txt

where install_dir is your SequeLink for JDBC Driver installation directory. This file can be edited as appropriate for your environment using any text editor. All parameters are configurable, but the most commonly configured parameters are:

Drivers	A list of colon-separated JDBC driver classes.
DefaultDriver	The default JDBC driver that appears in the Get Driver URL window.
Databases	A list of comma-separated JDBC URLs. The first item in the list appears as the default in the database selection window. You can use one of these URLs as a template when you make a JDBC connection. The default Config.txt file contains example URLs for most databases.
InitialContextFactory	Should be set to com.sun.jndi.fscontext.RefFSContextFactory if you are using file system data sources, or com.sun.jndi.ldap.LdapCtxFactory if you are using LDAP.
ContextProviderURL	The location of the .bindings file if you are using file system data sources, or your LDAP Provider URL if you are using LDAP.
Datasources	A list of comma-separated JDBC data sources. The first item in the list appears as the default in the data source selection window.

Starting DataDirect Test^™

How you start DataDirect Test depends on your platform:

As a Java application on Windows-Run the testforjdbc.bat file located in the testforjdbc directory.
As a Java application on UNIX-Run the testforjdbc.sh shell script located in the testforjdbc directory beneath the installation directory.

After you start DataDirect Test, the following window appears:

The main DataDirect Test window shows the following information:

In the Connection List box, a list of available connections.
In the JDBC/Database scroll box, a report indicating whether the last action succeeded or failed.
In the Java Code scroll box, the actual Java code used to implement the last action.

TIP: The DataDirect Test windows contain two Concatenate check boxes. Select a Concatenate check box to see a cumulative record of previous actions; otherwise, only the last action is shown. Selecting Concatenate can degrade performance, particularly when displaying large resultSets.

Connecting Using DataDirect Test^™

There are two methods to connect using DataDirect Test: through a data source or through driver/database selection.

Connecting Using a Data Source

From the DataDirect Test main window menu, select Connection / Connect to DB via Data Source. DataDirect Test displays the Select A Datasource window.





Select a data source from the Defined Datasources pane. In the User Name and Password fields, type the required user and password connection properties; then, click Connect. See "Developing JDBC Applications" for information about JDBC connection properties.
If the connection was successful, the Connection window appears and displays Connection Established in the JDBC/Database Output scroll box.

Connecting Using Driver/Database Selection

From the DataDirect Test main window menu, select Driver / Register Driver. DataDirect Test prompts you for a JDBC driver name.
In the Please Supply a Driver URL field, make sure that a driver is specified, as in the following example; then, click OK.

com.ddtek.jdbc.sequelink.SequeLinkDriver

If the JDBC driver was registered successfully, the main DataDirect Test window appears with a confirmation in the JDBC/Database Output scroll box.





Select Connection / Connect to DB from the main menu. JDBC prompts with a list of default connection URLs.





Select one of the default JDBC driver connection URLs. In the Database field, modify the default values of the connection URL appropriately for your environment.
In the User Name and Password fields, type the required user and password connection properties; then, click Connect. See "Developing JDBC Applications" for information about JDBC connection properties.
If the connection was successful, the Connection window appears and displays Connection Established in the JDBC/Database Output scroll box.

Executing a Simple Select Statement

This example explains how to execute a simple Select statement and retrieve the results.

From the Connection window menu, select Connection / Create Statement. The connection window indicates that the creation of the statement was successful.
Select Statement / Execute Stmt Query. DataDirect Test displays a dialog box that prompts for a SQL statement.
Specify the Select statement that you want to execute.





Click Submit; then, click Close.

Select Results / Show All Results. The data from your result set is displayed.





Scroll through the code in the Java Code scroll box to see which JDBC calls have been implemented by DataDirect Test.

Executing a Prepared Statement

This example explains how to execute a parameterized statement multiple times.

From the Connection window menu, select Connection / Create Prepared Statement. DataDirect Test prompts you for a SQL statement.
Specify the Insert statement that you want to execute.





Click Submit; then, click Close.

Select Statement / Set Prepared Parameters. To set the value and type for each parameter:
Type the parameter number.
Select the parameter type.
Type the parameter value.
Click Set to pass this information to the JDBC driver.





When you are finished, click Close.
Select Statement / Execute Stmt Update. The JDBC/Database Output scroll box indicates that one row has been inserted.





If you want to insert multiple records, repeat Step 3 and Step 5 for each record.
If you repeat the steps described in "Executing a Simple Select Statement", you will see that the previously inserted records are also returned.

Retrieving Database Metadata

From the Connection window menu, select Connection / Get DB Meta Data.
Select MetaData / Show Meta Data. Information about the JDBC driver and the database to which you are connected is returned.





Scroll through the Java code in the Java Code scroll box to find out which JDBC calls have been implemented by DataDirect Test.

Metadata also allows you to query the database catalog (enumerate the tables in the database, for example). In this example, we will query all tables that are owned by the user SCOTT.

Select MetaData / Tables.
In the Schema Pattern field, type SCOTT.





Click Ok. The Connection window indicates that getTables() succeeded.
Select Results / Show All Results. All tables owned by SCOTT are returned.

Scrolling Through a Result Set

NOTE: Scrollable result sets are supported by JDBC 2.0 and higher and require a Java 2 Platform (J2SE 1.4 or higher)-compatible Java Virtual Machine.

From the Connection window menu, select Connection / Create JDBC 2.0 Statement. DataDirect Test prompts you for a result set type and concurrency.
In the resultSetType field, select TYPE_SCROLL_SENSITIVE. In the resultSetConcurrency field, select CONCUR_READ_ONLY.





Click Submit; then, click Close.

Select Statement / Execute Stmt Query.
Specify the Select statement that you want to execute.





Click Submit; then, click Close.

Select Results / Scroll Results. The Scroll Result Set window indicates that the cursor is positioned before the first row.





Click the Absolute, Relative, Before, First, Prev, Next, Last, and After buttons as appropriate to navigate through the result set. After each action, the Scroll Result Set window displays the data at the current position of the cursor.





Click Close.

Batch Execution on a Prepared Statement

Batch execution on a prepared statement allows you to update or insert multiple records simultaneously. In some cases, this can significantly improve system performance because fewer round-trips to the database are required.

NOTE: Batch execution on a prepared statement is supported by the JDBC 2.0 and higher specifications and requires a Java 2 Platform (J2SE 1.4 or higher)-compatible Java Virtual Machine.

From the Connection window menu, select Connection / Create Prepared Statement.
Specify the Insert statement that you want to execute.





Click Submit; then, click Close.

Select Statement / Add Stmt Batch.
For each parameter:
Type the parameter number.
Select the parameter type.
Type the parameter value.
Click Set.





Click Add to add the specified set of parameters to the batch. To add multiple parameter sets to the batch, repeat Step 3 through Step 5 as many times as necessary. When you are finished adding parameter sets to the batch, click Close.
Select Statement / Execute Stmt Batch. DataDirect Test displays the rowcount for each of the elements in the batch.





If you re-execute the Select statement from "Executing a Simple Select Statement", you see that the previously inserted records are returned.

.

Returning ParameterMetaData

NOTE: Returning ParameterMetaData is a JDBC 3.0 feature and requires a J2SE 1.4 or higher Java Virtual Machine.

From the Connection window menu, select Connection / Create Prepared Statement.
Specify the prepared statement that you want to execute.





Click Submit; then, click Close.

Select Statement / Get ParameterMetaData. The Connection window displays ParameterMetaData.

Establishing Savepoints

NOTE: Savepoints is a JDBC 3.0 feature and requires a J2SE 1.4 or higher Java Virtual Machine.

From the Connection window menu, select Connection / Connection Properties.
Select TRANSACTION_COMMITTED from the Transaction Isolation drop-down list. Do not select the Auto Commit check box.





Click Set; then, click Close.

From the Connection window menu, select Connection / Load and Go. The Get Load And Go SQL window appears.
Specify the statement that you want to execute.





Click Submit.

Select Connection / Set Savepoint. In the Set Savepoints window, specify a savepoint name.





Click Apply; then, click Close. The Connection window indicates whether or not the savepoint succeeded.





Return to the Get Load And Go SQL window and specify another statement.





Click Submit.

Select Connection / Rollback Savepoint. In the Rollback Savepoints window, specify the savepoint name.





Click Apply; then, click Close. The Connection window indicates whether or not the savepoint rollback succeeded.





Return to the Get Load And Go SQL window and specify another statement.





Click Submit; then, click Close.

The Connection window displays data that was inserted before the first Savepoint. The second insert was rolled back.

Updatable Result Sets

The following examples illustrate Updatable result sets by deleting, inserting, and updating a row.

Deleting a Row

From the Connection window menu, select Connection / Create JDBC 2.0 Statement.
In the resultSetType field, select TYPE_SCROLL_SENSITIVE. In the resultSetConcurrency field, select CONCUR_UPDATABLE.





Click Submit; then, click Close.

Select Statement / Execute Stmt Query.
Specify the Select statement that you want to execute.





Click Submit; then, click Close.

Select Results / Inspect Results. The Inspect Result Set window is displayed.





Click Next. Current Row changes to 1.
Click Delete Row.
To verify the result, return to the Connection menu and select Connection / Load And Go. The Get Load and Go SQL window appears.
Specify the statement that you want to execute.





Click Submit; then, click Close.

The Connection window shows one row returned.

Inserting a Row

From the Connection window menu, select Connection / Create JDBC 2.0 Statement.
In the resultSetType field, select TYPE_SCROLL_SENSITIVE. In the resultSetConcurrency field, select CONCUR_UPDATABLE.





Click Submit; then, click Close.

Select Statement / Execute Stmt Query.
Specify the Select statement that you want to execute.





Click Submit; then, click Close.

Select Results / Inspect Results. The Inspect Result Set window is displayed.





Click Move to insert row; Current Row is now Insert row.
Change Data Type to int. In Set Cell Value, enter 20. Click Set Cell.
Select the second row in the top pane. Change the Data Type to String. In Set Cell Value, enter RESEARCH. Click Set Cell.
Select the third row in the top pane. In Set Cell Value, enter DALLAS. Click Set Cell.
Click Insert Row.
To verify the result, return to the Connection menu and select Connection / Load And Go. The Get Load and Go SQL window appears.
Specify the statement that you want to execute.





Click Submit; then, click Close.

The Connection window shows two rows returned.

Note that the ID will be 3 for the row just inserted, because it is an auto increment column.

Updating a Row

From the Connection window menu, select Connection / Create JDBC 2.0 Statement.
In the resultSetType field, select TYPE_SCROLL_SENSITIVE. In the resultSetConcurrency field, select CONCUR_UPDATABLE.





Click Submit; then, click Close.

Select Statement / Execute Stmt Query.
Specify the Select statement that you want to execute.





Click Submit; then, click Close.

Select Results / Inspect Results. The Inspect Result Set window is displayed.





Click Next. Current Row changes to 1.
In Set Cell Value, enter RALEIGH. Click Set Cell.
Click Update Row.
To verify the result, return to the Connection menu and select Connection / Load And Go. The Get Load and Go SQL window appears.
Specify the statement that you want to execute.





Click Submit; then, click Close.

The Connection window shows LOC for accounting changed from NEW YORK to RALEIGH.

LOB Support

NOTE: LOB support (Blobs and Clobs) is a JDBC 3.0 feature and requires a J2SE 1.4 or higher Java Virtual Machine.

The following example uses CLOB data; however, this procedure also applies to BLOB data. This example illustrates only one of several ways in which LOB data can be processed.

From the Connection window menu, select Connection / Create Statement.
Select Statement / Execute Stmt Query.
Specify the Select statement that you want to execute.





Click Submit; then, click Close.

Select Results / Inspect Results. The Inspect Result Set window is displayed.
Click Next. Current Row changes to 1.





Deselect Auto Traverse. This disables automatic traversal to the next row.
Click Get Cell.





Values are returned in the Get Cell Value field.
Change the Data Type to Clob.





Click Get Cell. The Clob data window appears.





Click Get Cell.





Values are returned in the Cell Value field.